<?php
class AAppSmsKuteNew extends CActiveRecord
{
    public function InsertApp($data)
    {
        $sql = "INSERT INTO c_sms_kute_event SET
            title=:title
            , alias=:alias
            , picture=:picture
            , introtext=:introtext
            , description=:description
            , status=:status
            , create_date=:create_date
        ";
        
        $command = Yii::app()->db->createCommand($sql);
        $command->bindValues($data);
        $result = $command->execute();
        
        $eventId = Yii::app()->db->getLastInsertID();
        
        /* Insert vào bảng build app sms */
        $sql = "SELECT id FROM c_user ORDER BY id";
        $command = Yii::app()->db->createCommand($sql);
        $user = $command->queryAll();
        
        $txt = "";        
        foreach($user as $row){
            $txt .= ", ('" . $eventId . "', '" . $row["id"] . "')";
        }        
        $txt = ltrim($txt, ", ");
        
        $sql = "INSERT INTO c_build_app_sms (eventId, userId) VALUES " . $txt;
        $command = Yii::app()->db->createCommand($sql);
        $command->execute();
        
        return $eventId;
    }
    
    public function UpdateApp($data)
    {
        $sql = "UPDATE c_sms_kute_event SET
            title=:title
            , alias=:alias
            , picture=:picture
            , introtext=:introtext
            , description=:description
            , status=:status
            WHERE id=:id            
        ";
        
        $command = Yii::app()->db->createCommand($sql);
        $command->bindValues($data);
        $result = $command->execute();
        return $result;
    }
    
    public function getAllApp()
    {
        $sql = "SELECT * FROM c_sms_kute_event ORDER BY create_date DESC";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        
        $list_id = "0";
        foreach($rows as $row){
            $list_id .= "," . $row["id"];
        }
        
        $sql = "SELECT count(id), eventId FROM c_sms_kute WHERE eventId IN (" . $list_id . ") GROUP BY eventId";
        $command = Yii::app()->db->createCommand($sql);
        $rowsEvent = $command->queryAll();
        $replaceEvent = array();
        foreach($rowsEvent as $row){
            $replaceEvent[$row["eventId"]] = $row["count(id)"];
        }
                
        foreach($rows as $i=>$row){
            $rows[$i]["total_sms"] = isset($replaceEvent[$row["id"]]) ? $replaceEvent[$row["id"]] : 0;
        }
        return $rows;
    }
    
    public function QuickUpdate($data)
    {
        $sql = "UPDATE c_sms_kute_event SET status=:status WHERE id=:id";
        $command = Yii::app()->db->createCommand($sql);
        $command->bindValues($data);
        $result = $command->execute();
        return $result;
    }
    
    public function DeleteApp($id)
    {
        $sql = "DELETE FROM c_sms_kute_event WHERE id=" . $id;
        $command = Yii::app()->db->createCommand($sql);
        $result = $command->execute();
        
        $sql = "DELETE FROM c_build_app_sms WHERE eventId=" . $id;
        $command = Yii::app()->db->createCommand($sql);
        $result = $command->execute();
        
        return $result;
    }
    
    public function GetEventInfo($id)
    {
        $sql = "SELECT * FROM c_sms_kute_event WHERE id=" . $id;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows;
    }
    
    public function getAllSms($search, $currentPage, $numberRecordPerPage)
    {
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        $condition = "";
        $condition .= !empty($search["title"]) ? " AND title LIKE ('%" . str_replace(" ", "%", $search["title"]) . "%')" : "";
        $condition .= !empty($search["toDate"]) ? " AND create_date>=" . strtotime($search["toDate"]) : "";
        $condition .= !empty($search["fromDate"]) ? " AND create_date<=" . strtotime($search["fromDate"]) : "";
        $condition .= $search["status"]!="" ? " AND status=" . intval($search["status"]) : "";
        $condition .= $search["price"]!="" ? " AND price=" . intval($search["price"]) : "";
        $condition .= $search["eventId"]!="" ? " AND eventId='" . $search["eventId"] . "'" : "";
        
        $sql = "SELECT * FROM c_sms_kute WHERE eventId!=0 " . $condition . " ORDER BY create_date DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll($sql);
        
        $list_event_id = "0";
        foreach($rows as $row){
            $list_event_id .= "," . $row["eventId"];   
        }
        
        $list_event_id = Common::remove_duplicate($list_event_id);
        $sql = "SELECT id, title FROM c_sms_kute_event WHERE id IN (" . $list_event_id . ")";
        $command = Yii::app()->db->createCommand($sql);
        $rowsEvent = $command->queryAll($sql);
        $replaceEvent = array();
        foreach($rowsEvent as $row){
            $replaceEvent[$row["id"]] = $row["title"];    
        }
        
        foreach($rows as $i=>$row){
            $rows[$i]["event_name"] = isset($replaceEvent[$row["eventId"]]) ? $replaceEvent[$row["eventId"]] : "";
        }
        
        return $rows;
    }
    
    public function getTotalSms($search)
    {
        $condition = "";
        $condition .= !empty($search["title"]) ? " AND title LIKE ('%" . str_replace(" ", "%", $search["title"]) . "%')" : "";
        $condition .= !empty($search["toDate"]) ? " AND create_date>=" . strtotime($search["toDate"]) : "";
        $condition .= !empty($search["fromDate"]) ? " AND create_date<=" . strtotime($search["fromDate"]) : "";
        $condition .= $search["status"]!="" ? " AND status=" . intval($search["status"]) : "";
        $condition .= $search["price"]!="" ? " AND price=" . intval($search["price"]) : "";
        $condition .= !empty($search["eventId"]) ? " AND eventId=" . $search["eventId"] : "";
        
        $sql = "SELECT count(id) FROM c_sms_kute WHERE eventId!=0 " . $condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow($sql);
        return $rows["count(id)"];    
    }
}
?>
